package weaver.splittable.db;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import weaver.conn.WeaverConnection;
import weaver.conn.WeaverStatement;
import weaver.splittable.STConfig;
import weaver.splittable.STContext;
import weaver.splittable.modal.InsertSqlInfo;
import weaver.splittable.modal.UpdateSqlInfo;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class STDBUtil {

    private static final Logger LOGGER = Logger.getLogger(STConfig.LOGGER_NAME);

    public static String createFullDataTable(String tableName) {
        return STContext.CREATE_TABLE().createFullDataTable(tableName);
    }

    public static String createIndex(String indexName, String tableName, List<String> columnNames) {
        return STContext.CREATE_INDEX().createIndex(indexName, tableName, columnNames);
    }

    /**
     * 检查表是否存在
     * @param tablename 表名
     * @param conn 数据库连接
     * @return 是否存在
     * @throws SQLException
     */
    public static Boolean checkTableIsExist(String tablename, WeaverConnection conn) throws SQLException {
        String sql = "";
        WeaverStatement st = null;
        String dbType = STConfig.DB_TYPE;
        try {
            st = (WeaverStatement) conn.createStatement();
            if(dbType.equals("mysql")) {
                sql = "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = '" + tablename + "' AND table_schema=DATABASE()";
                ResultSet rs = st.originExecuteQuery(sql);
                if (rs.next()) {
                    return true;
                }
                return false;
            } else if(dbType.equals("oracle")) {
                tablename = tablename.toUpperCase();
                sql = "SELECT * FROM User_Tables WHERE table_name = '" + tablename + "'";
                ResultSet rs = st.originExecuteQuery(sql);
                if (rs.next()) {
                    return true;
                }
                return false;
            } else if(dbType.equals("sqlserver")) {
                sql = "select name from sysobjects where xtype='u' and name = '" + tablename + "'";
                ResultSet rs = st.originExecuteQuery(sql);
                if (rs.next()) {
                    return true;
                }
                return false;
            } else if(dbType.equals("dm")) {
                tablename = tablename.toUpperCase();
                //sql = "SELECT * FROM User_Tables WHERE lower(table_name) = lower('" + tablename + "');";
                sql = "SELECT * FROM User_Tables WHERE table_name = '" + tablename + "'";
                ResultSet rs = st.originExecuteQuery(sql);
                if (rs.next()) {
                    return true;
                }
                return false;
            } else if(dbType.equals("st")) {
                tablename = tablename.toUpperCase();
                sql = "SELECT * FROM User_Tables WHERE table_name = '" + tablename + "'";
                ResultSet rs = st.originExecuteQuery(sql);
                if (rs.next()) {
                    return true;
                }
                return false;
            } else if(dbType.equals("pg")) {
                tablename = tablename.toLowerCase();
                sql = "select * from pg_class where relname = '" + tablename + "'";
                ResultSet rs = st.originExecuteQuery(sql);
                if (rs.next()) {
                    return true;
                }
                return false;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            if (st != null) {
                st.close();
            }
        }
        return false;
    }

    public static void insertBatch(String tableName, String appointTableName, List<String> idList, WeaverConnection conn, String dataIdColumn){
        PreparedStatement pst = null;
        tableName = tableName.toUpperCase();
        try {
            String idStr = StringUtils.join(idList.toArray(),",");
            List<InsertSqlInfo> insertSqlList = new ArrayList<>();
            if (StringUtils.isNotEmpty(idStr)){
                String sql = "select * from " + tableName + " where "+dataIdColumn+" in("+idStr+")";
                insertSqlList = STDBData2SqlUtil.getInsertForCompare(conn, sql, appointTableName);
            }
            if(insertSqlList!=null && insertSqlList.size()>0) {
                String sql = insertSqlList.get(0).getSql();
                pst = conn.prepareStatement(sql);
                for (int i = 0; i < insertSqlList.size(); i++) {
                    InsertSqlInfo insertSqlInfo = insertSqlList.get(i);
                    List<Object> objectList = insertSqlInfo.getObjectList();
                    int idx = 1;
                    for (Object o : objectList) {
                        pst.setObject(idx, o);
                        idx++;
                    }
                    pst.addBatch();
                }
                pst.executeBatch();
            }
        } catch (Exception e) {
            LOGGER.error("tableName:" + tableName + ",appointTableName:" + appointTableName + ",idList:" + idList + ",dataIdColumn:" + dataIdColumn);
            LOGGER.error("InsertBatch error:", e);
        } finally {
            if(pst!=null) {
                try {
                    pst.clearBatch();
                    pst.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static void updateBatch(String tableName, String appointTableName, List<String> idList, WeaverConnection conn, String dataIdColumn) {
        PreparedStatement pst = null;
        try {
            String idStr = StringUtils.join(idList.toArray(),",");
            List<UpdateSqlInfo> updateSqqlList = new ArrayList<>();
            if (!(idStr == null || idStr.isEmpty())){
                String sql2 = "select * from " + tableName + " where "+dataIdColumn+" in("+idStr+")";
                updateSqqlList = STDBData2SqlUtil.getUpdateForCompare(conn, sql2, appointTableName,dataIdColumn);
            }
            String sql = "";
            if (updateSqqlList.size()>0){
                sql = updateSqqlList.get(0).getSql();
            }
            pst = conn.originPreparedStatement(sql);
            for (int i = 0; i < updateSqqlList.size(); i++) {
                UpdateSqlInfo updateSqlInfo = updateSqqlList.get(i);
                List<Object> objectList = updateSqlInfo.getObjectList();
                long pkValue = updateSqlInfo.getPkValue();
                int idx = 1;
                for (Object o:objectList){
                    pst.setObject(idx,o);
                    idx++;
                }
                pst.setLong(idx,pkValue);
                pst.addBatch();
            }
            pst.executeBatch();
        } catch (Exception e) {
            LOGGER.error("tableName:" + tableName + ",appointTableName:" + appointTableName + ",idList:" + idList + ",dataIdColumn:" + dataIdColumn);
            LOGGER.error("UpdateBatch error:", e);
        } finally {
            if(pst!=null) {
                try {
                    pst.clearBatch();
                    pst.close();
                } catch (SQLException e) {
                }

            }
        }
    }

    public static void deleteDataForId(String tableName, List<String> idList, WeaverConnection conn, String dataIdColumn){
        WeaverStatement statement = null;
        try {
            statement = (WeaverStatement) conn.createStatement();
            String str = StringUtils.join(idList,",");
            String sql = "delete from "+tableName+" where "+dataIdColumn+" in (" + str + ")";
            statement.originExecuteUpdate(sql);
        } catch (Exception e) {
            LOGGER.error("deleteDataForId error tableName:" + tableName + ", idList:" + idList + ", keyStr:" + dataIdColumn);
            LOGGER.error("deleteDataForId error:", e);
        }finally {
            if(statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                }
            }
        }
    }

}
